Designating Keys
In general terms, a key field is a field that you can use to identify and retrieve a record in an indexed file. Key fields in APPX include a single primary and up to 16 alternate keys.
Primary keys provide the assumed key path to access and retrieve specific records in an indexed file. They also establish the linkage between files.For example, you might include a Customer Number field (the primary key to a Customer file) in a Sales Order file to link an order with additional information about the customer placing it. Unless otherwise specified by a user or designer, APPX always uses the primary key path. The field that is designated as the primary key should generally be short and unique, and representative of how data is most frequently accessed in the file. Examples of primary keys include a customer number in a Customer file, a product number in a Product file, and a date/time stamp in a Purchases History file.
Alternate keys provide optional key paths to access and retrieve specific records in an indexed file. A user or designer may optionally choose to change from a primary key path to any alternate key path for record access and retrieval. Examples of alternate keys to a customer file might include customer name, phone number, contact name, and/or address.
By using a primary key rather than an alternate key, you gain two referential integrity advantages.
Delete protection fields govern whether or not a user can delete a record while executing an input process. When used properly, these fields prevent the creation of orphan records because they prohibit deletion of records that are referenced by other records anywhere in the database.
Key protection fields control whether or not a user can modify the primary key to a record while executing an input process. The proper use of these fields also helps to maintain the integrity of a database because they prohibit modification of keys that are referenced by other records in the database.
Three significant restrictions apply to the definition of primary and alternate key fields in APPX:
Group trailers cannot be defined as key fields.
Each indexed file must contain one (and only one) primary key.
A maximum of 16 alternate keys can be defined for any file.
To maximize performance, you should minimize the number of key fields in each file. Unless you use a field for one of the following purposes, it should not be defined as a key:
Online data retrieval. For example, you may define a customer number as a primary key so that your application users can retrieve specific customer records for display. Or perhaps, you might create a duplicate alternate key based on the customer number in a Sales Invoices file to display invoices online in customer number sequence.
To relate data in one record to data in another, either within the same file or among files. Remember that primary keys are the only fields that can be used to automatically maintain referential integrity with the Delete Protection and Key Protection options.
Data lookups for items in a process. If you include a customer number field on a sales order record, your users can automatically scan customer information when entering an order, if the customer number is a key to the customer file. In addition, APPX uses a key field to access related information when generating a standard subheading image in an output process.
A unique key prohibits an application user from entering more than one record with the same value in a key field. The term duplicate keys refers to a user's ability to define two or more records with the same value in a key field.
Unique key fields provide key paths that you can use as record identifiers. To be able to identify a specific record in a file, you must define at least one key as unique. The primary key is generally designated as the unique key in a file.
Unique keys are also used for online edit checking. If a user attempts to add a record with a value in a unique key field that corresponds to a record already in a file, APPX does not permit the user to save the entry. APPX considers this an error condition and prohibits the user from adding the record.
Duplicate key fields are used for online record retrieval in a predetermined order. For example, to display sales orders on the screen in customer number sequence, Customer Number is defined as a duplicate alternate key to the Sales Orders file. The trade-off for this capability is that duplicate keys decrease performance and increase storage requirements.
|